var mssql = require('mssql');
var mysql = require('mysql');

module.exports = new(function (config) {
    this.config = config || sysConfig.mssql;

    this.new = function (config) {
        return Object.assign({}, this, {
            config: config
        });
    };

    this.execMapper = function (method, args, callback) {
        if (typeof args == 'function') {
            callback = args;
            args = null;
        }
        exec(this.config, parseSql(method(args), args), args, callback);
    };

    this.execMapperSync = function (method, args) {
        return execSync(this.config, parseSql(method(args), args), args);
    };

    this.exec = function (sql, args, callback) {
        if (typeof args == 'function') {
            callback = args;
            args = null;
        }
        exec(this.config, parseSql(sql, args), args, callback);
    };

    this.execSync = function (sql, args) {
        return execSync(this.config, parseSql(sql, args), args);
    };

    this.execTransaction = function (sql, args, callback) {
        if (typeof args == 'function') {
            callback = args;
            args = null;
        }
        execTransaction(this.config, parseSql(sql, args), args, callback);
    };

    this.execTransactionSync = function (sql, args) {
        return execTransactionSync(this.config, parseSql(sql, args), args);
    }

    this.escape = mysql.escape;

    return this;
})();

function parseSql(sql, args) {
    if (args) {
        var reg = /\{([^\{\}]+?)\}/g;
        var ms = sql.match(reg);
        if (ms != null) {
            for (var i = 0; i < ms.length; i++) {
                var mkeys = ms[i].substring(1, ms[i].length - 1).split('||');
                if (!args[mkeys[0].trim()] || args[mkeys[0].trim()].toStr() == '') {
                    if (mkeys.length == 1) {
                        sql = sql.replace(ms[i], '\'\'');
                    } else {
                        sql = sql.replace(ms[i], '');
                    }
                } else if (mkeys.length == 1) {
                    sql = sql.replace(ms[i], mysql.escape(args[mkeys[0].trim()].toStr()).replace(/#/g, '＃'));
                } else {
                    sql = sql.replace(ms[i], mkeys[1].trim());
                }
            }
        }

        reg = /#([^\s'%"!=,]+)/g;
        ms = sql.match(reg);
        if (ms == null) {
            return sql.replace(/＃/g, '#');
        }
        for (var i = 0; i < ms.length; i++) {
            var key = ms[i].substring(1).trim();
            if (!args[key] || args[key].toStr() == '') {
                sql = sql.replace(ms[i], '');
            } else {
                var v = mysql.escape(args[key].toStr());
                sql = sql.replace(ms[i], v.substr(1, v.length - 2).replace(/#/g, '＃'));
            }
        }
        return sql.replace(/＃/g, '#');
    } else {
        return sql.replace(/＃/g, '#');
    }
}

function getPool(config, obj, exec) {
    var key = config.server + config.database;
    if (!global.mssqlPool) {
        global.mssqlPool = Object.create(null);
    }
    if (!global.mssqlPool[key]) {
        global.mssqlPool[key] = new mssql.ConnectionPool(config).connect(err => {
            if (err) {
                logger.error(err);
                if (obj.callback) {
                    if (obj.type == 'sync') {
                        obj.callback({
                            err: err,
                            rows: null,
                            args: obj.args
                        });
                    } else {
                        obj.callback(err, null, obj.args);
                    }
                }
                return;
            } else {
                exec(global.mssqlPool[key]);
            }
        });
    } else if (!global.mssqlPool[key].connected) {
        setTimeout(function () {
            getPool(config, obj, exec);
        }, 200);
    } else {
        exec(global.mssqlPool[key]);
    }
}

function exec(config, sql, args, callback) {
    getPool(config, {
        type: '',
        callback: callback,
        args: args
    }, function (pool) {
        var conn = pool.request();
        conn.multiple = sysConfig.multipleStatements;
        conn.query(sql, function (err, rows) {
            if (err) {
                logger.error(err);
                callback && callback(err, null, args);
                return;
            }

            callback && callback(null, rows, args);
        });
    });
}

function execSync(config, sql, args) {
    return new Promise((resolve) => {
        getPool(config, {
            type: 'sync',
            callback: resolve,
            args: args
        }, function (pool) {
            var conn = pool.request();
            conn.multiple = sysConfig.multipleStatements;
            conn.query(sql, function (err, rows) {
                if (err) {
                    logger.error(err);
                    resolve({
                        err: err,
                        rows: null,
                        args: args
                    });
                    return;
                }

                resolve({
                    err: err,
                    rows: rows,
                    args: args
                });
            });
        });
    });
}

function execTransaction(config, sql, args, callback) {
    getPool(config, {
        type: '',
        callback: callback,
        args: args
    }, function (pool) {
        var trans = new mssql.Transaction(pool);
        trans.begin(function (err) {
            if (err) {
                logger.error(err);
                callback && callback(err, null, args);
                return;
            }

            var conn = new mssql.Request(trans);
            conn.multiple = sysConfig.multipleStatements;
            conn.query(sql, function (err, rows) {
                if (err) {
                    logger.error(err);
                    trans.rollback(function () {
                        callback && callback(err, null, args);
                    });
                    return;
                }
                trans.commit(function (err) {
                    if (err) {
                        logger.error(err);
                        trans.rollback(function () {
                            callback && callback(err, null, args);
                        });
                        return;
                    }

                    callback && callback(null, rows, args);
                });
            });
        });
    });
}

function execTransactionSync(config, sql, args) {
    return new Promise((resolve) => {
        getPool(config, {
            type: 'sync',
            callback: resolve,
            args: args
        }, function (pool) {
            var trans = new mssql.Transaction(pool);
            trans.begin(function (err) {
                if (err) {
                    logger.error(err);
                    resolve({
                        err: err,
                        rows: null,
                        args: args
                    });
                    return;
                }

                var conn = new mssql.Request(trans);
                conn.multiple = sysConfig.multipleStatements;
                conn.query(sql, function (err, rows) {
                    if (err) {
                        logger.error(err);
                        trans.rollback(function () {
                            resolve({
                                err: err,
                                rows: null,
                                args: args
                            });
                        });
                        return;
                    }
                    trans.commit(function (err) {
                        if (err) {
                            logger.error(err);
                            trans.rollback(function () {
                                resolve({
                                    err: err,
                                    rows: null,
                                    args: args
                                });
                            });
                            return;
                        }

                        resolve({
                            err: err,
                            rows: rows,
                            args: args
                        });
                    });
                });
            });
        });
    });
}